import os
import xlwings as xw
import pandas as pd

app = xw.App(visible=True, add_book=False)
file_with_folder = '../Files/上半年销售统计表.xlsx'
workbook = app.books.open(file_with_folder)
data = []  # 用于存放书名

for worksheet in workbook.sheets:
    values = worksheet['A2'].expand('down').value
    data = data + values

data = list(set(data))  # 去掉重复的书名
data.insert(0, '书名')  # 在去重后的书名前加标题列
new_workbook = xw.books.add()
worksheet_new = new_workbook.sheets.add('书名')
worksheet_new['A1'].clear()
worksheet_new['A1'].options(index=False, transpose=True).value = data
worksheet_new.autofit()
new_workbook.save('../Files/666.xlsx')
new_workbook.close()
workbook.close()
app.quit()
